<?php
function connectDB()
{
  $host = "localhost";
  $user = "alexey";
  $pass = "alex_lazer";
  $link_id = mysql_connect($host, $user, $pass);
  mysql_select_db('fly', $link_id) or die(mysql_error());
  mysql_query("SET NAMES 'utf8'");
  mysql_query("SET CHARACTER SET 'utf8'");
  return $link_id;
}

function startTransaction()
{
  mysql_query("SET AUTOCOMMIT=0");
  mysql_query("BEGIN");
}

function commitTransaction()
{
  mysql_query("COMMIT");
}

function rollbackTransaction()
{
  mysql_query("ROLLBACK");
}

function init()
{
  session_start();
  if ($_SESSION['aid'] != '') {
    return true;
  } else {
    return false;
  }
}

function loginUser($login, $pass)
{
  connectDB();
  startTransaction();

  $password = encryptString($login, $pass);

  $resultSet = mysql_query("SELECT
                                    users.id
                              FROM
                                    users
                              INNER JOIN usrgroup ON users.id = usrgroup.userid
                              INNER JOIN groups ON usrgroup.groupid = groups.id
                              WHERE
                                        nick = '" . mysql_real_escape_string($login) . "'
                                    AND
                                        password = '" . mysql_real_escape_string($password) . "'
                                    AND
                                        groups.header = 'Admin'") or die(mysql_error());


  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      session_start();
      $_SESSION['aid'] = $row['id'];
      return true;
    } else {
      return false;
    }
  } else {
    rollbackTransaction();
    return false;
  }
}

function encryptString($login, $password)
{
  $tmpLogin = hash("tiger192,3", $login);
  $tmpConnector = hash("gost", "theFlyNet");
  $tmpPassword = hash("haval224,3", $password);
  $encryptedString = hash("snefru256", $tmpLogin . $tmpConnector . $tmpPassword);
  return $encryptedString;
}

function getUsers($start, $end)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    id,
                                    nick,
                                    registered,
                                    email
                              FROM users
                              WHERE blocked = 0
                              ORDER BY nick
                              LIMIT " . mysql_real_escape_string($start - 1) . ", " . mysql_real_escape_string($end)) or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function countUsers()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT COUNT(id) AS us FROM users") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      return $row['us'];
    } else {
      return 0;
    }

  } else {
    rollbackTransaction();
    return 0;
  }

}

function pagesFromUsers($users)
{
  $pages = ceil($users / 30);
  return $pages;
}

function pagesFromNews($news)
{
  $pages = ceil($news / 30);
  return $pages;
}

function countNews()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT COUNT(id) AS us FROM news") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      return $row['us'];
    } else {
      return 0;
    }

  } else {
    rollbackTransaction();
    return 0;
  }
}

function getUsersForDelete()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    users.id,
                                    users.nick,
                                    date,
                                    registered
                              FROM
                                    deleteusers
                              INNER JOIN users ON deleteusers.userid = users.id
                              ORDER BY date") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }

}

function getBlockedUsers()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    id,
                                    nick,
                                    registered,
                                    email
                              FROM users
                              WHERE blocked = 1
                              ORDER BY nick") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function generatePassword($length)
{
  $chars = 'abdefhiknrstyzABDEFGHKNQRSTYZ23456789';
  $numChars = strlen($chars);
  $string = '';
  for ($i = 0; $i < $length; $i++) {
    $string .= substr($chars, rand(1, $numChars) - 1, 1);
  }
  return $string;
}

function getNews($start, $end)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    news.id,
                                    header,
                                    news.date
                              FROM news
                              ORDER BY news.date DESC
                              LIMIT " . mysql_real_escape_string($start - 1) . ", " . mysql_real_escape_string($end)) or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function countNewsResponses($newsId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    COUNT(id) AS msg
                              FROM responses
                              WHERE newsid='" . $newsId . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      return $row['msg'];
    } else {
      return 0;
    }
  } else {
    rollbackTransaction();
    return 0;
  }
}

function getFormattedTime()
{
  $microtime = microtime();
  $microtime = str_replace(" ", "", $microtime);
  $microtime = str_replace(".", "", $microtime);
  return $microtime;

}

function getAllGroups()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    id,
                                    header
                              FROM
                                    groups
                              ORDER BY id") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }

}

function countGroupMembers($groupId)
{
  connectDB();
  startTransaction();

  $userGroupsSet = mysql_query("SELECT COUNT(userid) AS users
                                        FROM usrgroup
                                        WHERE groupid='" . mysql_real_escape_string($groupId) . "'");

  if ($userGroupsSet) {
    commitTransaction();
    $users = mysql_fetch_assoc($userGroupsSet);
    return $users['users'];
  } else {
    rollbackTransaction();
    return 0;
  }
}

function getGroupMembers($groupId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    users.id,
                                    users.nick,
                                    users.registered,
                                    users.email
                              FROM
                                    usrgroup
                              INNER JOIN users ON usrgroup.userid = users.id
                              INNER JOIN groups ON usrgroup.groupid = groups.id

                              WHERE
                                    usrgroup.groupid = '" . $groupId . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getGroupWaitingUsers()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    groups.id AS gid,
                                    users.id AS uid,
                                    users.nick,
                                    groups.header,
                                    users.email
                              FROM
                                    entergroup
                              INNER JOIN users ON entergroup.userid = users.id
                              INNER JOIN groups ON entergroup.groupid = groups.id") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function searchUsers($userName)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query(" SELECT
                                     id,
                                     nick,
                                     email,
                                     rating,
                                     messages
                               FROM
                                     users
                               WHERE
                                        nick LIKE '%" . mysql_real_escape_string($userName) . "%'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getAllSections()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    id,
                                    header
                              FROM
                                    sections") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }

}

function countSectionTopics($sectionId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    COUNT(id) AS tpcs
                              FROM
                                    topics
                              WHERE
                                    sectionid = '" . mysql_real_escape_string($sectionId) . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      return $row['tpcs'];
    } else {
      return 0;
    }
  } else {
    rollbackTransaction();
    return 0;
  }
}

function getAllTopics()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    topics.header,
                                    topics.id,
                                    users.nick AS author,
                                    groups.header AS tgroup
                              FROM
                                    topics
                              INNER JOIN users ON topics.authorid = users.id
                              INNER JOIN groups ON topics.groupid = groups.id
                              ORDER BY id") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getTopicModerators($topicId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    topics.id,
                                    topics.header,
                                    users.email,
                                    users.nick,
                                    users.id AS uid
                              FROM
                                    moderators
                              INNER JOIN users ON moderators.userid = users.id
                              INNER JOIN topics ON moderators.topicid = topics.id
                              WHERE topicid = '" . mysql_real_escape_string($topicId) . "'
                              ORDER BY id") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getNewTopics()
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    newtopics.id,
                                    newtopics.header,
                                    users.email,
                                    users.nick,
                                    users.id AS uid,
                                    sections.id AS sid,
                                    sections.header AS section
                              FROM
                                    newtopics
                              INNER JOIN users ON newtopics.authorid = users.id
                              INNER JOIN sections ON newtopics.section = sections.id
                              ORDER BY id") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getNewTopicInfo($topicId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    newtopics.id,
                                    section,
                                    header,
                                    content,
                                    users.id AS uid,
                                    newtopics.langid AS langid
                              FROM
                                    newtopics
                              INNER JOIN users ON newtopics.authorid = users.id
                              WHERE newtopics.id='" . $topicId . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function isModerator($userId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    id
                              FROM
                                    usrgroup
                              WHERE
                                    userid = '" . mysql_real_escape_string($userId) . "'
                              AND
                                    groupid = (SELECT id FROM groups WHERE header = 'Moderators')") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      return true;
    } else {
      return false;
    }
  } else {
    rollbackTransaction();
    return false;
  }
}

function redirect($url)
{
  echo "<script language='JavaScript'>
        location.href = '" . $url . "'
    </script>";
}

function getNewNewsComments($newsId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    tmpresponses.id,
                                    users.nick AS author,
                                    content,
                                    date,
                                    time
                              FROM
                                    tmpresponses
                              INNER JOIN
                                    users ON tmpresponses.userid = users.id
                              WHERE
                                    newsid = '" . mysql_real_escape_string($newsId) . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getNewNewsCommentById($commentId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    newsid,
                                    userid,
                                    content,
                                    date,
                                    time,
                                    quote
                              FROM
                                    tmpresponses
                              WHERE
                                    tmpresponses.id = '" . mysql_real_escape_string($commentId) . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getNewCommentImages($commentId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT
                                    id,
                                    path
                              FROM
                                    tmprespimages
                              WHERE
                                    respid = '" . mysql_real_escape_string($commentId) . "'") or die(mysql_error());

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getUserMail($userId)
{
  connectDB();
  startTransaction();

  $resultSet = mysql_query("SELECT email FROM users WHERE id = '" . mysql_real_escape_string($userId) . "'");

  if ($resultSet) {
    commitTransaction();
    if ($row = mysql_fetch_assoc($resultSet)) {
      return $row['email'];
    } else return "";
  } else {
    rollbackTransaction();
    return null;
  }
}

function getLocalTimeStamp()
{
  date_default_timezone_get("Europe/Minsk");
  return getdate()[0];
}

function f_Date($timeStamp)
{
  date_default_timezone_set('Europe/Minsk');
  return date("Y-m-d", $timeStamp);
}

function f_Time($timeStamp)
{
  date_default_timezone_set('Europe/Minsk');
  return date("H:i:s", $timeStamp);
}

function getLastVisits()
{
  connectDB();
  startTransaction();
  $timeStamp = getLocalTimeStamp();

  $resultSet = mysql_query("SELECT
                                date AS visitdate,
                                time AS visittime,
                                users.nick,
                                users.id,
                                visits.id AS vid
                            FROM visits
                            INNER JOIN users ON users.id = visits.userid
                            WHERE visits.date >= " . mysql_real_escape_string(f_Date($timeStamp)) . "
                            ORDER BY visits.time DESC
                            ");

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}

function getAllVisits()
{
  connectDB();
  startTransaction();
  $resultSet = mysql_query("SELECT
                                date AS visitdate,
                                time AS visittime,
                                users.nick,
                                users.id,
                                visits.id AS vid
                            FROM visits
                            INNER JOIN users ON users.id = visits.userid
                            ORDER BY visits.time DESC
                            ");

  if ($resultSet) {
    commitTransaction();
    return $resultSet;
  } else {
    rollbackTransaction();
    return null;
  }
}